home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Magnum One
/
Magnum One (Mid-American Digital) (Disc Manufacturing).iso
/
d13
/
qptech.arc
/
TI582.ASC
< prev
next >
Wrap
Text File
|
1991-04-25
|
16KB
|
595 lines
PRODUCT : Quattro Pro NUMBER : 582
VERSION : All
OS : DOS
DATE : March 28, 1991 PAGE : 1/8
TITLE : Entering and Manipulating Date and Time Values
What is a Date?
In Quattro Pro, dates and times are stored as values rather than
labels. The date number represents the number of days between
the current date and December 30, 1899. December 30, 1899 is day
zero, and each day after that has its own unique number. This
number is referred to as the Date Serial Number. For example,
the date serial number 33065 represents July 11, 1990 (i.e. the
33065th day since December 30, 1899).
These date serial numbers allow Quattro Pro to perform
mathematical calculations on dates and times. Labels, on the
other hand, always have a value of zero, and therefore cannot
easily be used in time calculations.
Time is stored as a fraction of a day, and is always a value
between zero and one. This number is commonly referred to as the
Time Serial Number. For example, 0.00 is 12:00 AM, 0.25 is 6:00
AM, 0.75 is 6:00 PM, 0.324 is 7:46:34 AM, and 0.689 is 4:32:10
PM.
The date and time serial numbers can be stored in a single cell
(i.e. the @NOW function), or individual cells (i.e the @TODAY
function). The date serial number and time serial number can be
combined in the same cell by adding them together. For example,
33065.893345 represents the combined date/time serial number for
July 11, 1990 at 9:26:25 PM.
Formatting a Cell
The display of a cell can be different from what is actually
stored in the cell. The display is called the format of a cell.
For example, the contents of a cell may be the date serial number
33065, but onscreen, the cell may display "11-Jul-90". A cell
can be formatted for a date or time by selecting /Style|Numeric
Format|Date (or Date|Time), or by executing the CTRL-D shortcut
key combination, which automatically formats your entry as a
valid date.
PRODUCT : Quattro Pro NUMBER : 582
VERSION : All
OS : DOS
DATE : March 28, 1991 PAGE : 2/8
TITLE : Entering and Manipulating Date and Time Values
VALID DATE FORMAT EXAMPLE
-----------------------------------------------------------------
(DD-MMM-YY) 11-JUL-90
(DD-MMM) 11-JUL
(MMM-YY) JUL-90
(Long Int'l) {Default is (MM/DD/YY)} 07/11/90
(Short Int'l) {Default is (MM/DD)} 07/11
The default cell display format is determined by the keystrokes
used to enter the data, such as the CTRL-D, or by the /Options|
International|Date (or Time) settings. /Options|International
sets the global display options for all spreadsheets, and sets
the sequence of arguments for entering dates. For example,
DD/MM/YY format can be set as the default, so that July 11, 1990
is entered (and displayed) as 11/07/90 instead of 7/11/90.
Changing this option does not effect any cells specifically
formatted with /Style|Numeric Format.
When using a combined date/time serial number, a cell can be
formatted for either a date or a time, but not both.
Entering Date and Time Values
There are four methods for entering date and time values:
1. @Functions
2. CTRL-D
3. /Database|Data Entry
4. Manually entering actual date/time serial number value.
Using @functions
There are several @functions you can use to enter dates and
times. @DATE accepts three parameters and converts them into a
date serial number. Likewise, @TIME converts three parameters
into a time serial number. @DATEVALUE and @TIMEVALUE convert
text strings that are in a valid date format and time format into
date serial numbers and time serial numbers, respectively.
@TODAY looks at the computer's internal clock and inserts the
current date serial number. @NOW goes one step further and
PRODUCT : Quattro Pro NUMBER : 582
VERSION : All
OS : DOS
DATE : March 28, 1991 PAGE : 3/8
TITLE : Entering and Manipulating Date and Time Values
inserts the current date/time serial number. Since @TODAY and
@NOW take information from the system clock, they are
recalculated each time a spreadsheet is loaded, or the F9 (CALC)
key is pressed. The examples in the following table are for the
time of 9:26:25 PM on July 11, 1990:
FUNCTION CONVERT FROM CONVERT TO EXAMPLE
----------------------------------------------------------------------------
@NOW Computer Clock Date/Time Serial Number
@NOW = 33065.89335
@TODAY Computer Clock Date Serial Number @TODAY = 33065
@DATE YR,MO,DAY Date Serial Number @DATE(90,7,11) = 33065
@TIME HR,MIN,SEC Time Serial Number @TIME(21,26,25) = 0.89335
@DATEVALUE Date String Date Serial Number @DATEVALUE("11-Jul-90") =
33065
@TIMEVALUE Time String Time Serial Number @TIMEVALUE("9:26:25PM") =
0.89335
Using the CTRL-D shortcut key
The CTRL-D shortcut key lets you enter a date in a valid date
format. Quattro Pro automatically places the appropriate date
serial number in the cell and formats it to display as a date.
Similarly, CTRL-D preceding a time format of HH:MM:SS where hours
are in military time or HH:MM:SS(AM or PM) with hours in standard
time places the time serial number in the cell and formats the
cell to display as a time. For example, after pressing CTRL-D
and entering 7/11/90. The screen shows 7/11/90 and the date
serial number 33065 shows on the edit line as the actual contents
of the cell.
Using the /Database|Data Entry command
The best way to enter a large number of dates and/or times is to
use /Database|Data Entry|Dates Only. This lets you select an
area where dates (or times) are the only data that can be
entered. If a cell has been formatted for Dates Only, you don't
have to precede your entries with CTRL-D. The edit line displays
DATE, only valid date entries are allowed, and the date/time
serial number does not show on the edit line or in the cell.
This also makes it easier to edit the date/time. For example, if
PRODUCT : Quattro Pro NUMBER : 582
VERSION : All
OS : DOS
DATE : March 28, 1991 PAGE : 4/8
TITLE : Entering and Manipulating Date and Time Values
a cell was formatted with /Database|Data Entry|Dates Only,
entering 7/11/90 shows just that in the cell. The edit line also
shows 7/11/90 preceded by the word DATE. If 7-11-90 was entered,
the error message "Invalid date or time" will display because
that is not a valid date format.
Manually entering date/time serial number values
A date/time value can be entered just like any other number. The
cell can then be formatted to show the desired date and/or time
format. It is the date and time display formats that make these
values different from any other number. For example, if the
number 33065 is entered, choosing /Style|Numeric Format|Date|Long
Int'l makes the cell display 07/11/90.
Manipulating date and/or time serial numbers
There are several @functions that are useful in evaluating a date
and/or time serial number and retrieving information from it.
For a date serial number, the @YEAR, @MONTH, and @DAY functions
return the specific year, month, and day of that number.
Likewise, the @HOUR, @MINUTE, and @SECOND functions return the
hour, minute, and second information for a time serial number.
FUNCTION CONVERT FROM CONVERT TO EXAMPLE
----------------------------------------------------------------------------
@DAY Date Serial Number Day of Month @DAY(33065) = 11
@MONTH Date Serial Number Month of Year @MONTH(33065) = 7
@YEAR Date Serial Number Year of Century @YEAR(33065) = 90
@SECOND Time Serial Number Second of Minute @SECOND(0.89335) = 25
@MINUTE Time Serial Number Minute of Hour @MINUTE(0.89335) = 26
@HOUR Time Serial Number Hour of Day @HOUR(0.89335) = 21
Calculating elapsed time
To determine the elapsed time between starting and ending date or
time values, subtract the start value from the end value. The
trick is converting this number into a useful form. If the
result is formatted as a date, it displays as a date late in the
year 1899 because time is a fraction of a day. Quattro Pro
assumes the calculation is referring to day 1, which is December
PRODUCT : Quattro Pro NUMBER : 582
VERSION : All
OS : DOS
DATE : March 28, 1991 PAGE : 5/8
TITLE : Entering and Manipulating Date and Time Values
30, 1899. In most cases, the date and time serial number should
be combined in the same cell when dealing with elapsed time.
This will cover the case where the end value has passed midnight
into the next day. If the date and time are not combined and
time values are entered, the difference of two cells displays as
a negative number if the ending time has passed midnight, or an
incorrect number if the difference has passed a 24-hour period.
For example, if a beginning time of 9:26:25 PM (which has the
time serial number of 0.893345) and an ending time of 7:41:36 AM
(which has the time serial number of 0.320556) are entered,
subtracting the beginning time from the ending time will result
in the number -0.572789 (which displays as 10:15:11 AM when
formatted for time). Obviously, this is not the correct answer.
The problem is that the elapsed time has passed midnight. Simply
subtracting the two time values gives an incorrect (in this case
negative) elapsed time. The solution? Combine the date serial
number with the time serial number, creating a combined date/time
serial number for both the beginning and ending times, before
subtracting one from the other.
To correct the example above, assume the beginning time is
9:26:25 PM July 11, 1990 (date serial number 33065), and the
ending time is 7:41:36 AM July 12, 1990 (date serial number
33066). Combining the date and time serial number results in the
beginning number 33065.893345 and the ending number 33066.320556.
Subtracting the beginning number from the ending number gives the
value 0.427211, which you can convert to hours, minutes, and
seconds.
Three additional @functions that useful in calculating elapsed
time: @INT, @MOD, and @ROUND. @INT evaluates a real number
(combined integer and decimal) and returns only the integer
portion of that number.
Similar to the @INT function, @MOD is used to return the decimal
portion of a real number. Since @MOD divides the first argument
("X") by the second argument ("Y"), the second argument ("Y")
should be set to 1 when doing elapsed time calculations. If "Y"
is not set to 1, the correct remainder will not returned.
@ROUND is used to change the accuracy of a number to the closest
whole number. For instance, in the following example, there is
PRODUCT : Quattro Pro NUMBER : 582
VERSION : All
OS : DOS
DATE : March 28, 1991 PAGE : 6/8
TITLE : Entering and Manipulating Date and Time Values
no need to deal with partial seconds, so we round the real number
10.94400014 to the next whole second, 11.
PRODUCT : Quattro Pro NUMBER : 582
VERSION : All
OS : DOS
DATE : March 28, 1991 PAGE : 7/8
TITLE : Entering and Manipulating Date and Time Values
FUNCTION DESCRIPTION OF FUNCTION EXAMPLE
-----------------------------------------------------------------
@INT(X) Drops Fraction of X, Returning Integer Value
@INT(5.35) = 5
@MOD(X,Y) Divides X Value by Y and Returns Remainder Value
@MOD(5.35,1) = 0.35
@ROUND(X,NUM) Adjusts Precision of X to NUM Decimal Points
@ROUND(5.35,1) = 5.4
The following example shows how to calculate the number of days
and hours between a beginning and ending date and time. The
concept here is to:
1. Input two date/time values.
2. Calculate the difference between the values giving elapsed
time.
3. Separate the integer and decimal portions of the elapsed
time.
4. Convert the decimal portion into a different measure (i.e.
hours to minutes).
5. Repeat steps 3 & 4 until the desired precision is reached.
Cells A2 and B2 are used as input date cells in this example.
Enter the following information into the cells indicated (The
semicolons and the descriptions following them can be omitted):
A1: 'Begin
B1: 'End
C1: 'Elapsed
D1: 'Days
E1: 'Part Days
F1: 'Part Day(Hr) ;Partial days measured in hours
A2: 33065.89335
B2: 33070.32056
C2: +B2-A2 ;The difference between the two times
D2: @INT(C2) ;Integer portion of elapsed time returns days
E2: @MOD(C2,1) ;Remainder portion returns partial days
PRODUCT : Quattro Pro NUMBER : 582
VERSION : All
OS : DOS
DATE : March 28, 1991 PAGE : 8/8
TITLE : Entering and Manipulating Date and Time Values
F2: +E2*24 ;Converts the remainder from days to hours
1. Set columns A through F to a column width of 12 using
/Style|Block Widths|Set Width|A1..F1|12.
2. Use /Style|Alignment|Center|A1..F2 to center the labels
and numbers.
3. Format cells A2 and B2 to a time format using
/Style|Numeric Format|Date|Time.
Note that cells A2 and B2 each contain a combined date/time
serial number formatted for time, and thus do not indicate the
date onscreen. Remember, a cell cannot be formatted for both a
date and a time.
Here is how the example looks for the two values entered:
Begin │ End │ Elapsed │ Days │ Part Days │Part Day(Hr)
───────────┼───────────┼─────────┼────────┼───────────┼─────────────
09:26:25 PM│07:41:36 AM│ 4.42721 │ 4 │ 0.42721 │ 10.25304
The same concept can be used to calculate the hours, minutes and
seconds elapsed. The following is an addition to the above
example, adding hours, minutes, and seconds.
G1: 'Hours
H1: 'Part Hrs
I1: 'Part Hrs(Min) ;Partial hours measured in minutes
J1: 'Minutes
K1: 'Part Mins
L1: 'Part Min(Sec) ;Partial minutes measured in seconds
M1: 'Seconds
G2: @INT(F2) ;Integer portion returns whole hours
H2: @MOD(F2,1) ;Remainder portion returns partial hours
I2: +H2*60 ;Converts decimal from hours to minutes
J2: @INT(I2) ;Integer portion returns whole minutes
K2: @MOD(I2,1) ;Remainder portion returns partial minutes
L2: +K2*60 ;Converts decimal from minutes to seconds
M2: @ROUND(L2,0) ;Rounds decimal to whole seconds
1. Set columns G through M to a column width of 12 using
/Style|Block Widths|Set Width|G1..M1|12.
PRODUCT : Quattro Pro NUMBER : 582
VERSION : All
OS : DOS
DATE : March 28, 1991 PAGE : 9/8
TITLE : Entering and Manipulating Date and Time Values
2. Use /Style|Alignment|Center|G1..M2 to center the labels
and numbers.
3. Use /Style|Hide Columns to hide columns C, E, F, H, I, K,
and L.
The hidden column option suppresses the screen display and
printing of intermediate steps, which may clutter the ending
results. The spreadsheet will look like the following:
Begin │ End │ Days │ Hours │ Minutes │ Seconds
───────────┼───────────┼──────────┼──────────┼──────────┼─────────
09:26:25 PM│07:41:36 AM│ 4 │ 10 │ 15 │ 11